//
//  Database.m
//  cliqset
//
//  Created by David Hinson on 2/16/09.
//  Copyright 2009 Cliqset, Inc.. All rights reserved.
//

#import "Database.h"
#import "Contact.h"
#import "Event.h"
#import "Message.h"
#import "cliqsetAppDelegate.h"

@implementation Database

-(Database*)init {
	return self;
}

-(void)dealloc {
	[super dealloc];
}

#pragma mark ---- Database ----

// Creates a writable copy of the bundled default database in the application Documents directory.
- (void)createEditableCopyOfDatabaseIfNeeded {
	
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
	NSString *writableDBPath = [self getDatabasePath];
    success = [fileManager fileExistsAtPath:writableDBPath];
	
    if (success) return;
	
	// The writable database does not exist, so copy the default to the appropriate location.
	NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"cliqset.sqlite"];
	success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
	if (!success) {
		NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
	}
}

// Get latest event contact from Database
-(NSString*)getLatestContactDateFromDatabase {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	NSMutableString* lastContactDate=@"";
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT MAX(LastUpdate) FROM Contacts";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				lastContactDate = (NSMutableString*)[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			}
		}
		else {
			//NSAssert1(0, @"Error selecting Contacts with message '%s'.", sqlite3_errmsg(database));
		}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
	
	return lastContactDate;
}

// Get latest event date from Database
-(NSString*)getLatestEventDateFromDatabase {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	NSMutableString* lastEventDate=@"";
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT MAX(CreateDate) FROM Events";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				lastEventDate = (NSMutableString*)[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			}
		}
		else {
			NSAssert1(0, @"Error selecting Events with message '%s'.", sqlite3_errmsg(database));
		}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
	
	return lastEventDate;
}

// Count Contacts From Database
-(int)countContactsFromDatabase {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	int count;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT Count(*) FROM Contacts";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				count = sqlite3_column_int(statement, 0);
			}
		}
		else {
			//NSAssert1(0, @"Error selecting Contacts with message '%s'.", sqlite3_errmsg(database));
		}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
	
	return count;
}

// Count Events From Database
-(int)countEventsFromDatabase {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	int count;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT Count(*) FROM Events";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				count = sqlite3_column_int(statement, 0);
			}
		}
		else {
			NSAssert1(0, @"Error selecting Events with message '%s'.", sqlite3_errmsg(database));
		}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
	
	return count;
}

// Count Countries From Database
-(int)countCountriesFromDatabase {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	int count;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT Count(*) FROM Countries";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				count = sqlite3_column_int(statement, 0);
			}
		}
		else {
			NSAssert1(0, @"Error selecting Countries with message '%s'.", sqlite3_errmsg(database));
		}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
	
	return count;
}

// show contact
-(void)showContacts {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT UserIdentifier, ContactIdentifier FROM Contacts";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				NSLog(@"%@ : %@", [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)], [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)]);
			}
		}
		else {
			//NSAssert1(0, @"Error selecting Contacts with message '%s'.", sqlite3_errmsg(database));
		}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
}

// Load Countries From Database
-(void)loadCountriesFromDatabase:(int)index code:(NSMutableString**)code name:(NSMutableString**)name {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	sqlite3_stmt *statement;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT Code, Name FROM Countries WHERE rowid=?";
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_int(statement, 1, index);
				
        while (sqlite3_step(statement) == SQLITE_ROW) {
			
			*code = [[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] copy];
			*name = [[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] copy];
		}
	}
	else {
		NSAssert1(0, @"Error selecting Addresses with message '%s'.", sqlite3_errmsg(database));
	}
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	
	sqlite3_finalize(statement);
	sqlite3_close(database);
}

// Do we need to update our database?
-(BOOL)databaseNeedsUpdate {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	NSString *dateTime;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
        const char *sql = "SELECT nullif(LastUpdate,'') FROM Version";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
				dateTime  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
				NSLog(@"%@", dateTime);
            }
        }
		else {
			NSAssert1(0, @"Error selecting Version with message '%s'.", sqlite3_errmsg(database));
		}
		
        sqlite3_finalize(statement);
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	sqlite3_close(database);
	
	if ([dateTime isEqualToString:@"0000-00-00"]) {
		return true;
	} else {
		return false;
	}
}

// Load Addresses From Database
-(void)loadAddressesFromDatabase:(sqlite3 *)database uid:(NSString *)uid cid:(NSString *)cid contact:(Contact *)contact {
	
	sqlite3_busy_timeout(database, 500);
		
	const char * sql = "SELECT ifnull(type,''), ifnull(StreetAddress,''), ifnull(locality,''), ifnull(region,''), ifnull(country,''), ifnull(postCode,''), primaryAddress FROM Addresses WHERE UserIdentifier=? AND ContactIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset Addresses array
		// contact.addresses = [[NSMutableArray alloc] init];
		[contact.addresses removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new Address record
			Address *address = [[Address alloc] init];
			
			// Populate Address record
			address.type          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			address.streetAddress = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			address.locality      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
			address.region        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
			address.country       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
			address.postalCode    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
			address.primary       = sqlite3_column_int(statement, 6);
			
			// Add Address record
			[contact.addresses addObject:address];
			
			// Release Address record
			[address release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting Addresses with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load Email Addresses From Database
-(void)loadEmailAddressesFromDatabase:(sqlite3 *)database uid:(NSString *)uid cid:(NSString *)cid contact:(Contact *)contact {
	
	sqlite3_busy_timeout(database, 500);
		
	const char * sql = "SELECT ifnull(type,''), ifnull(address,''), primaryAddress FROM EmailAddresses WHERE UserIdentifier=? AND ContactIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset Email Addresses array
		// contact.emailAddresses = [[NSMutableArray alloc] init];
		[contact.emailAddresses removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new Email Address record
			EmailAddress *email = [[EmailAddress alloc] init];
			
			// Populate Email Address record
			email.type        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			email.address     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			email.primary     = sqlite3_column_int(statement, 2);
			
			// Add Email Address record
			[contact.emailAddresses addObject:email];
			
			// Release Email Address record
			[email release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting Email Addresses with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load Phones From Database
-(void)loadPhonesFromDatabase:(sqlite3 *)database uid:(NSString *)uid cid:(NSString *)cid contact:(Contact *)contact {
	
	sqlite3_busy_timeout(database, 500);
		
	const char * sql = "SELECT ifnull(type,''), ifnull(phoneNumber,''), primaryPhone FROM Phones WHERE UserIdentifier=? AND ContactIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset Phones array
		// contact.phones = [[NSMutableArray alloc] init];
		[contact.phones removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new Phone record
			Phone *phone = [[Phone alloc] init];
			
			// Populate Phone record
			phone.type        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			phone.phoneNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			phone.primary     = sqlite3_column_int(statement, 2);
			
			// Add Phone record
			[contact.phones addObject:phone];
			
			// Release Phone record
			[phone release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting Phones with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load IMs From Database
-(void)loadIMsFromDatabase:(sqlite3 *)database uid:(NSString *)uid cid:(NSString *)cid contact:(Contact *)contact {
	
	sqlite3_busy_timeout(database, 500);
		
	const char * sql = "SELECT ifnull(network,''), ifnull(userName,''), primaryNetwork FROM IMs WHERE UserIdentifier=? AND ContactIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset IMs array
		// contact.imScreenNames = [[NSMutableArray alloc] init];
		[contact.imScreenNames removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new IM record
			IM *im = [[IM alloc] init];
			
			// Populate IM record
			im.network  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			im.name     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			im.primary  = sqlite3_column_int(statement, 2);
			
			// Add IM record
			[contact.imScreenNames addObject:im];
			
			// Release IM record
			[im release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting IMs with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load URLs From Database
-(void)loadURLsFromDatabase:(sqlite3 *)database uid:(NSString *)uid cid:(NSString *)cid contact:(Contact *)contact {
	
	sqlite3_busy_timeout(database, 500);
	
	const char * sql = "SELECT ifnull(type,''), ifnull(url,'') FROM URLs WHERE UserIdentifier=? AND ContactIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset URLs array
		// contact.urls = [[NSMutableArray alloc] init];
		[contact.urls removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new URL record
			URL *url = [[URL alloc] init];
			
			// Populate IM record
			url.type  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			url.url   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			
			// Add URL record
			[contact.urls addObject:url];
			
			// Release URL record
			[url release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting URLs with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load Tags From Database
-(void)loadTagsFromDatabase:(sqlite3 *)database uid:(NSString *)uid cid:(NSString *)cid contact:(Contact *)contact {
	
	sqlite3_busy_timeout(database, 500);
	
	const char * sql = "SELECT ifnull(tag,'') FROM Tags WHERE UserIdentifier=? AND ContactIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset Tags array
		// contact.tags = [[NSMutableArray alloc] init];
		[contact.tags removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new Tag record
			Tag *tag = [[Tag alloc] init];
			
			// Populate Tag record
			tag.tag  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			
			// Add Tag record
			[contact.tags addObject:tag];
			
			// Release Tag record
			[tag release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting Tags with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load Resources From Database
-(void)loadResourcesFromDatabase:(sqlite3 *)database rid:(NSString *)rid event:(Event *)event {
	
	sqlite3_busy_timeout(database, 500);
	
	const char * sql = "SELECT ifnull(ResourceName,''), ifnull(ResourceValue,''), ResourceBlob FROM Resources WHERE EventIdentifier=?";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement, 1, [rid UTF8String], -1, SQLITE_TRANSIENT);
		
		// Reset resources array
		[event.resources removeAllObjects];
		
        while (sqlite3_step(statement) == SQLITE_ROW) {
			// Allocate new Resource record
			Resource *resource = [[Resource alloc] init];
			
			// Populate Resource record
			resource.name  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			resource.value = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			resource.blob  = [NSData dataWithBytes:sqlite3_column_blob(statement, 2) length:sqlite3_column_bytes(statement, 2)];			
			// Add Resource record
			[event.resources addObject:resource];
			
			// Release Resource record
			[resource release];
		}
	}
	else {
		NSAssert1(0, @"Error selecting Resources with message '%s'.", sqlite3_errmsg(database));
	}
	
	sqlite3_finalize(statement);
}

// Load events into memory
-(void)loadEventsFromDatabase:(NSString *)uid {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSAutoreleasePool *pool    = [[NSAutoreleasePool alloc] init];			
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT ifnull(EventIdentifier,''), ifnull(CreateDate,''), ifnull(EventType,''), ifnull(Title,''), ifnull(Body,''), ifnull(URL,''), ifnull(ConsumerKey,''), ifnull(ServiceName,''), ifnull(ServiceLogoURL,''), ServiceLogoBlob, ifnull(ServiceIconURL,''), ServiceIconBlob, ifnull(ServiceWebsiteURL,''), ifnull(UserGivenName,''), ifnull(UserMiddleName,''), ifnull(UserFamilyName,''), ifnull(UserAvatarURL,''), UserAvatarBlob, UserUserIdentifier, ifnull(UserGender,'') FROM Events WHERE UserIdentifier=? ORDER BY createDate DESC";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			
			// Reset events array
			[myApp.toEvents removeAllObjects];
			
			int iCount = 0;
            while (sqlite3_step(statement) == SQLITE_ROW) {

				// Allocate new contact record
				Event *event = [[Event alloc] init];
				
				// Populate event record
				event.eventIdentifier            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
				event.createDate                 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
				event.eventType                  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
				event.title                      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
				event.body                       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
				event.url                        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
				event.sourceService.consumerKey  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
				event.sourceService.name         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
				event.sourceService.logoURL      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
				event.sourceService.logoBlob     = [NSData dataWithBytes:sqlite3_column_blob(statement, 9) length:sqlite3_column_bytes(statement, 9)];
				event.sourceService.iconURL      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
				event.sourceService.iconBlob     = [NSData dataWithBytes:sqlite3_column_blob(statement, 11) length:sqlite3_column_bytes(statement, 11)];
				event.sourceService.websiteURL   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
				event.sourceUser.givenName       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
				event.sourceUser.middleName      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
				event.sourceUser.familyName      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
				event.sourceUser.avatarURL       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
				event.sourceUser.avatarBlob      = [NSData dataWithBytes:sqlite3_column_blob(statement, 17) length:sqlite3_column_bytes(statement, 17)];
				event.userIdentifier             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
				event.sourceUser.gender          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
				
				[self loadResourcesFromDatabase:database rid:event.eventIdentifier event:event];

				// Add event record
				[myApp.toEvents addObject:event];
				
				// Release event record
				[event release];
				
				// Just get the top 25
				if (++iCount > 25) {
					break;
				}
            }
        }
		else {
			NSAssert1(0, @"Error selecting Events with message '%s'.", sqlite3_errmsg(database));
		}
		
        sqlite3_finalize(statement);
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	sqlite3_close(database);
	[pool release];
}
	
// Load contacts into memory
-(void)loadContactsFromDatabase:(NSString *)uid {
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSAutoreleasePool *pool    = [[NSAutoreleasePool alloc] init];			

	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT ifnull(givenName,''), ifnull(middleName,''), ifnull(familyName,''), ifnull(notes,''), ifnull(company,''), ifnull(position,''), ifnull(photoURL,''), photo, ContactIdentifier FROM Contacts WHERE UserIdentifier=? ORDER BY familyName, givenName";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			
			// Reset contacts array
			// myApp.contacts = [[NSMutableArray alloc] init];
			[myApp.contacts removeAllObjects];
			
            while (sqlite3_step(statement) == SQLITE_ROW) {
				// Allocate new contact record
				Contact *contact = [[Contact alloc] init];
				
				// Populate contact record
				contact.givenName         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
				contact.middleName        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
				contact.familyName        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
				contact.notes             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
				contact.company           = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
				contact.position          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
				contact.photoURL          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
				contact.photoBlob         = [NSData dataWithBytes:sqlite3_column_blob(statement, 7) length:sqlite3_column_bytes(statement, 7)];
				contact.contactIdentifier = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
				
				// Load URLs
				[self loadURLsFromDatabase:database uid:myApp.userString cid:contact.contactIdentifier contact:contact];
				// Load Tags
				[self loadTagsFromDatabase:database uid:myApp.userString cid:contact.contactIdentifier contact:contact];
				// Load Addresses
				[self loadAddressesFromDatabase:database uid:myApp.userString cid:contact.contactIdentifier contact:contact];
				// Load Email Addresses
				[self loadEmailAddressesFromDatabase:database uid:myApp.userString cid:contact.contactIdentifier contact:contact];
				// Load Phones
				[self loadPhonesFromDatabase:database uid:myApp.userString cid:contact.contactIdentifier contact:contact];
				// Load IMs
				[self loadIMsFromDatabase:database uid:myApp.userString cid:contact.contactIdentifier contact:contact];
				
				// Add contact record
				[myApp.contacts addObject:contact];
				
				// Release contact record
				[contact release];
            }
        }
		else {
			//NSAssert1(0, @"Error selecting Contacts with message '%s'.", sqlite3_errmsg(database));
		}
		
        sqlite3_finalize(statement);
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	sqlite3_close(database);
	[pool release];
}

// Execute sqlite statment
-(void)executeSqlite:(sqlite3 *)database  sql:(const char*)sql {
	sqlite3_stmt *statement;
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_step(statement);
	}
	else {
		NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
	}		
	sqlite3_finalize(statement);
}

// Remove Contact
-(void)removeContact:(NSString*)uid cid:(NSString*)cid {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM Contacts WHERE UserIdentifier=? AND ContactIdentifier=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove Contact from the database with message '%s'.", sqlite3_errmsg(database));
			} else {
				// We have removed the contact successfully, now we must remove any favorite tagged.
				[self removeFavorite:uid cid:cid];
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove Update Contact
-(void)removeUpdateContact:(sqlite3*)database uid:(NSString*)uid cid:(NSString*)cid {
	sqlite3_stmt *statement;
	static char *sql = "DELETE FROM Contacts WHERE UserIdentifier=? AND ContactIdentifier=?";
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		int success = sqlite3_step(statement);
		if (success == SQLITE_ERROR) {
			NSAssert1(0, @"Error: failed to remove Contact from the database with message '%s'.", sqlite3_errmsg(database));
			} 
		else {
			// We have removed the contact successfully, now we must remove any favorite tagged.
			[self removeUpdateFavorite:database uid:uid cid:cid];
			}
		}
}

// Add Favorite
-(void)addFavorite:(NSString*)uid cid:(NSString*)cid {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "INSERT INTO Favorites (UserIdentifier, ContactIdentifier) VALUES (?, ?)";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to insert Favorite into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Put URL
-(void)putURL:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag content:(NSString*)content {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		char buffer[1024];
		
		NSString * deleteString = [@"" stringByAppendingFormat:@"DELETE FROM URLs WHERE UserIdentifier='%@' AND ContactIdentifier='%@' AND Type='%@'", uid, cid, tag];
		NSString * insertString = [@"" stringByAppendingFormat:@"INSERT INTO URLs (UserIdentifier, ContactIdentifier, Type, URL) VALUES ('%@','%@','%@','%@')", uid, cid, tag, content];
		
		// Delete any existing tag
		[deleteString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];		
		
		// Insert new tag 
		[insertString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];	
		
	} else {
		// Even though the open failed, call close to properly clean up resources.
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		// Additional error handling, as appropriate...
	}
	sqlite3_close(database);
}

// Put Phone
-(void)putPhone:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag content:(NSString*)content flag:(BOOL)flag {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		char buffer[1024];
		
		NSString * deleteString = [@"" stringByAppendingFormat:@"DELETE FROM Phones WHERE UserIdentifier='%@' AND ContactIdentifier='%@' AND Type='%@'", uid, cid, tag];
		NSString * insertString = [@"" stringByAppendingFormat:@"INSERT INTO Phones (UserIdentifier, ContactIdentifier, Type, PhoneNumber, PrimaryPhone) VALUES ('%@','%@','%@','%@',%@)", uid, cid, tag, content, (flag ? @"1" : @"0")];
		
		// Delete any existing tag
		[deleteString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];		
		
		// Insert new tag 
		[insertString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];	
		
	} else {
		// Even though the open failed, call close to properly clean up resources.
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		// Additional error handling, as appropriate...
	}
	sqlite3_close(database);
}

// Put Email Address
-(void)putEmailAddress:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag content:(NSString*)content flag:(BOOL)flag {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;

    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		char buffer[1024];
		
		NSString * deleteString = [@"" stringByAppendingFormat:@"DELETE FROM EmailAddresses WHERE UserIdentifier='%@' AND ContactIdentifier='%@' AND Type='%@'", uid, cid, tag];
		NSString * insertString = [@"" stringByAppendingFormat:@"INSERT INTO EmailAddresses (UserIdentifier, ContactIdentifier, Type, Address, PrimaryAddress) VALUES ('%@','%@','%@','%@',%@)", uid, cid, tag, content, (flag ? @"1" : @"0")];

		// Delete any existing tag
		[deleteString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];		
		
		// Insert new tag 
		[insertString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];	
		
		} else {
			// Even though the open failed, call close to properly clean up resources.
			NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
			// Additional error handling, as appropriate...
		}
	sqlite3_close(database);
}

// Put Address
-(void)putAddress:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag street:(NSString*)streetAddress locality:(NSString*)locality region:(NSString*)region postalCode:(NSString*)postalCode country:(NSString*)country {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		char buffer[1024];
		
		NSString * deleteString = [@"" stringByAppendingFormat:@"DELETE FROM Addresses WHERE UserIdentifier='%@' AND ContactIdentifier='%@' AND Type='%@'", uid, cid, tag];
		NSString * insertString = [@"" stringByAppendingFormat:@"INSERT INTO Addresses (UserIdentifier, ContactIdentifier, Type, StreetAddress, Locality, Region, PostCode, Country) VALUES ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", uid, cid, tag, streetAddress, locality, region, postalCode, country];
		
		// Delete any existing tag
		[deleteString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];		
		
		// Insert new tag 
		[insertString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];	
		
	} else {
		// Even though the open failed, call close to properly clean up resources.
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		// Additional error handling, as appropriate...
	}
	sqlite3_close(database);
}

// Put IM
-(void)putIM:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag content:(NSString*)content flag:(BOOL)flag {
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		char buffer[1024];
		
		NSString * deleteString = [@"" stringByAppendingFormat:@"DELETE FROM IMs WHERE UserIdentifier='%@' AND ContactIdentifier='%@' AND Network='%@'", uid, cid, tag];
		NSString * insertString = [@"" stringByAppendingFormat:@"INSERT INTO IMs (UserIdentifier, ContactIdentifier, Network, UserName, PrimaryNetwork) VALUES ('%@','%@','%@','%@',%@)", uid, cid, tag, content, (flag ? @"1" : @"0")];
		
		// Delete any existing tag
		[deleteString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];		
		
		// Insert new tag 
		[insertString getCString:buffer maxLength:sizeof(buffer) encoding:NSUTF8StringEncoding];
		[self executeSqlite:database sql:buffer];	
		
	} else {
		// Even though the open failed, call close to properly clean up resources.
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		// Additional error handling, as appropriate...
	}
	sqlite3_close(database);
}

// Put Contact
-(void)putContact:(Contact*)contact {

	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	
	//[self showContacts];
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_stmt *statement;
		//static char *sql = "DELETE FROM Contacts WHERE UserIdentifier=? AND ContactIdentifier=?";
		//if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		//	sqlite3_bind_text(statement,  1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
		//	sqlite3_bind_text(statement,  2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
		//	int success = sqlite3_step(statement);
		//	if (success == SQLITE_ERROR) {
		//		NSAssert1(0, @"Error: failed to remove Contact from the database with message '%s'.", sqlite3_errmsg(database));
		//	}
		//
		//}

		//sqlite3_finalize(statement);
			
		static char *sql = "UPDATE Contacts SET givenName=?, middleName=?, familyName=?, notes=?, position=?, company=?, photoURL=? WHERE UserIdentifier=? AND ContactIdentifier=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [contact.givenName UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [contact.middleName UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  3, [contact.familyName UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  4, [contact.notes UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  5, [contact.position UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  6, [contact.company UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  7, [contact.photoURL UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  8, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  9, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to insert Contact into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}

		sqlite3_finalize(statement);
		
		// Process emails
		for (int x=0; x<[contact.emailAddresses count]; x++) {
			EmailAddress * email = (EmailAddress*)[contact.emailAddresses objectAtIndex:x];
			[self putEmailAddress:myApp.userString cid:contact.contactIdentifier tag:email.type content:email.address flag:email.primary];
		}

		// Process IMs
		for (int x=0; x<[contact.imScreenNames count]; x++) {
			IM * im = (IM*)[contact.imScreenNames objectAtIndex:x];
			[self putIM:myApp.userString cid:contact.contactIdentifier tag:im.network content:im.name flag:im.primary];
		}
		
		// Process URLs
		for (int x=0; x<[contact.urls count]; x++) {
			URL * url = (URL*)[contact.urls objectAtIndex:x];
			[self putURL:myApp.userString cid:contact.contactIdentifier tag:url.type content:url.url];
		}
		
		// Process Phones
		for (int x=0; x<[contact.phones count]; x++) {
			Phone * phone = (Phone*)[contact.phones objectAtIndex:x];
			[self putPhone:myApp.userString cid:contact.contactIdentifier tag:phone.type content:phone.phoneNumber flag:phone.primary];
		}
		
		// Process Addresses
		for (int x=0; x<[contact.addresses count]; x++) {
			Address * address = (Address*)[contact.addresses objectAtIndex:x];
			[self putAddress:myApp.userString cid:contact.contactIdentifier tag:address.type street:address.streetAddress locality:address.locality region:address.region postalCode:address.postalCode country:address.country];
		}
		
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
	}
	sqlite3_close(database);
}

// Remove Email Address
-(void)removeEmailAddress:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM EmailAddresses WHERE UserIdentifier=? AND ContactIdentifier=? AND Type=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  3, [tag UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove Email Address from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove Address
-(void)removeAddress:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM Addresses WHERE UserIdentifier=? AND ContactIdentifier=? AND Type=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  3, [tag UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove Address from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove IM
-(void)removeIM:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM IMs WHERE UserIdentifier=? AND ContactIdentifier=? AND Network=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  3, [tag UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove IM from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove URL
-(void)removeURL:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM URLs WHERE UserIdentifier=? AND ContactIdentifier=? AND Type=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  3, [tag UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove URL from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove Phone
-(void)removePhone:(NSString*)uid cid:(NSString*)cid tag:(NSString *)tag {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM Phones WHERE UserIdentifier=? AND ContactIdentifier=? AND Type=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  3, [tag UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove Phone from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove Tag
-(void)removeTags:(sqlite3*)database uid:(NSString*)uid cid:(NSString*)cid {	
	sqlite3_stmt *statement;
	static char *sql = "DELETE FROM Tags WHERE UserIdentifier=? AND ContactIdentifier=?";
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		//sqlite3_bind_text(statement,  3, [tag UTF8String], -1, SQLITE_TRANSIENT);
		int success = sqlite3_step(statement);
		if (success == SQLITE_ERROR) {
			NSAssert1(0, @"Error: failed to remove Tag from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
}

// Remove Favorite
-(void)removeFavorite:(NSString*)uid cid:(NSString*)cid {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM Favorites WHERE UserIdentifier=? AND ContactIdentifier=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove Favorite from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Remove Update Favorite
-(void)removeUpdateFavorite:(sqlite3*)database uid:(NSString*)uid cid:(NSString*)cid {
	sqlite3_stmt *statement;
	static char *sql = "DELETE FROM Favorites WHERE UserIdentifier=? AND ContactIdentifier=?";
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		sqlite3_bind_text(statement,  1, [uid UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		int success = sqlite3_step(statement);
		if (success == SQLITE_ERROR) {
			NSAssert1(0, @"Error: failed to remove Favorite from the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
}

// Is Contact a Favorite?
-(void)loadFavorites:(NSString*)uid {
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSAutoreleasePool *pool    = [[NSAutoreleasePool alloc] init];			

	NSString *path = [self getDatabasePath];
	sqlite3 *database;

	[myApp.favorites removeAllObjects];
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT Favorites.contactIdentifier FROM Favorites INNER JOIN Contacts ON (Favorites.UserIdentifier=Favorites.UserIdentifier AND Favorites.ContactIdentifier=Contacts.ContactIdentifier) WHERE Favorites.UserIdentifier=? ORDER BY Contacts.FamilyName, Contacts.GivenName";
		sqlite3_stmt *statement;
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			
			while (sqlite3_step(statement) == SQLITE_ROW) {
				NSString * contactIdentifier = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
				[myApp.favorites addObject:contactIdentifier];
			}
		}
		else {
			NSAssert1(0, @"Error selecting Favorites with message '%s'.", sqlite3_errmsg(database));
		}
		
		sqlite3_finalize(statement);
    } else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	sqlite3_close(database);
	[pool release];
}

// Is Contact a Favorite?
-(int)isContactAFavorite:(NSString*)uid cid:(NSString*)cid {
	
	int iExists = 0;
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;

    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		sqlite3_busy_timeout(database, 500);
		
		const char * sql = "SELECT contactIdentifier FROM Favorites WHERE UserIdentifier=? AND ContactIdentifier=?";
		sqlite3_stmt *statement;
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [uid UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [cid UTF8String], -1, SQLITE_TRANSIENT);
		
			if (sqlite3_step(statement) == SQLITE_ROW) {
				iExists = 1;
				}
			}
		else {
			NSAssert1(0, @"Error selecting Favorites with message '%s'.", sqlite3_errmsg(database));
		}
	
		sqlite3_finalize(statement);
		} 
	else {
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		}
	sqlite3_close(database);
	
	return iExists;
}

// Clear event tables for a reload
-(void)clearEventTables:(sqlite3 *)database {	
	// Delete all from Events
	[self executeSqlite:database sql:"DELETE FROM Events"];		
	// Delete all from Resources
	[self executeSqlite:database sql:"DELETE FROM Resources"];		
}

// Clear tables for a reload
-(void)clearTables {	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		// Delete all from Contacts
		[self executeSqlite:database sql:"DELETE FROM Contacts"];		
		// Delete all from Addresses
		[self executeSqlite:database sql:"DELETE FROM Addresses"];
		// Delete all from Email Addresses
		[self executeSqlite:database sql:"DELETE FROM EmailAddresses"];		
		// Delete all from IMs
		[self executeSqlite:database sql:"DELETE FROM IMs"];		
		// Delete all from Phones
		[self executeSqlite:database sql:"DELETE FROM Phones"];		
		// Delete all from Tags
		[self executeSqlite:database sql:"DELETE FROM Tags"];
		// Delete all from URLs
		[self executeSqlite:database sql:"DELETE FROM URLs"];
		// Update Version File to say we have been updated
		[self executeSqlite:database sql:"UPDATE Version Set LastUpdate=date('now')"];
	}
	else {
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_close(database);
}

// Reset timestamp
-(void)resetTimestamp {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		// Update Version File to say we have NOT been updated
		[self executeSqlite:database sql:"UPDATE Version Set LastUpdate='0000-00-00'"];
		} 
	else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Populate contacts
-(void)populateContacts:(sqlite3*) database index:(int)index {

	NSLog(@"Contact %d", index);
	//return;
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO Contacts (UserIdentifier, ContactIdentifier, GivenName, MiddleName, FamilyName, Notes, Company, Position, PhotoURL, Photo, LastUpdate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	//static char *sql = "INSERT INTO Contacts (UserIdentifier, ContactIdentifier, GivenName, MiddleName, FamilyName, Notes, Company, Position, PhotoURL, LastUpdate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		Contact *contact = [myApp.contacts objectAtIndex:index];
		
		// Grab an image of the contact, using the user identifier and contact identifier.
		NSString *imageURL        = contact.photoURL;
		
		NSURL *url                = [NSURL URLWithString:imageURL];
		NSURLRequest* request     = [NSURLRequest requestWithURL:url cachePolicy:NSURLRequestReloadIgnoringLocalCacheData timeoutInterval:10.0];
		NSURLResponse* response;
		NSError* error;
		//NSData *imageData;
		//UIImage * dummy = [UIImage imageNamed:@"Dummy.png"];
		//imageData       = (NSData*)UIImageJPEGRepresentation(dummy, 1.0);
		NSData* imageData         = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:&error];
		contact.photoBlob         = (NSMutableData*)imageData;
		
		sqlite3_bind_text(statement,  1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  3, [contact.givenName UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  4, [contact.middleName UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  5, [contact.familyName UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  6, [contact.notes UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  7, [contact.company UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  8, [contact.position UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  9, [contact.photoURL UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_blob(statement, 10, [imageData bytes], [imageData length], SQLITE_TRANSIENT);
		
		NSDateFormatter *sISO8601 = [[NSDateFormatter alloc] init];
		sISO8601.timeStyle        = NSDateFormatterFullStyle;
		//sISO8601.dateFormat       = @"yyyy-MM-dd'T'HH:mm:ss.SSSZZZ";	
		sISO8601.dateFormat       = @"yyyy-MM-dd'T'HH:mm:ss.SSS";
		
		NSTimeZone * timeZone     =[NSTimeZone timeZoneWithAbbreviation:@"GMT"];
		[sISO8601 setTimeZone:timeZone];
		NSDate * currentDate      = [[NSDate alloc] init];
		
		NSMutableString * lastUpdate = (NSMutableString*)[sISO8601 stringFromDate:currentDate];
		
		NSLog(@"last update - sqlite: %@", lastUpdate);
		
		lastUpdate = (NSMutableString*)[lastUpdate stringByAppendingString:@"Z"];
		
		[sISO8601 release];
		
		sqlite3_bind_text(statement, 11, [lastUpdate UTF8String], -1, SQLITE_TRANSIENT);
		
		// Remove any old record
		[self removeUpdateContact:database uid:myApp.userString cid:contact.contactIdentifier];
		
		int success = sqlite3_step(statement);
		if (success == SQLITE_ERROR) {
			//NSAssert1(0, @"Error: failed to insert Contact into the database with message '%s'.", sqlite3_errmsg(database));
		}
		
		//[imageData release];
	}
	else {
		NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
	}		
	sqlite3_finalize(statement);
}

// Populate Resources
-(void)populateResources:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO Resources (EventIdentifier, ResourceName, ResourceValue, ResourceBlob) VALUES (?, ?, ?, ?)";
	Event *event = [myApp.toEvents objectAtIndex:index];
	
	for (int x=0; x<[event.resources count]; x++) {
		Resource* resource = (Resource *)[event.resources objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [event.eventIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [resource.name UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [resource.value UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_blob(statement, 4, [resource.blob bytes], [resource.blob length], SQLITE_TRANSIENT);
			
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to insert Resource into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate events
-(void)populateEvents:(sqlite3*) database index:(int)index {
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO Events (UserIdentifier, EventIdentifier, CreateDate, EventType, Title, Body, URL, ConsumerKey, ServiceName, ServiceLogoURL, ServiceLogoBlob, ServiceIconURL, ServiceIconBlob, ServiceWebsiteURL, UserGivenName, UserMiddleName, UserFamilyName, UserHonorificPrefix, UserHonorificSuffix, UserBirthdate, UserPoliticalViews, UserReligiousViews, UserGender, UserLocality, UserRegion, UserCountry, UserPostCode, UserMaritalStatus, UserSexualOrientation, UserAvatarBlob, UserAvatarURL, UserUserIdentifier) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
		Event *event = [myApp.toEvents objectAtIndex:index];
		
		NSMutableData * avatar = [event.sourceUser.avatarBlob copy];
		
		sqlite3_bind_text(statement,  1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  2, [event.eventIdentifier UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  3, [event.createDate UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  4, [event.eventType UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  5, [event.title UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  6, [event.body UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  7, [event.url UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  8, [event.sourceService.consumerKey UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  9, [event.sourceService.name UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  10, [event.sourceService.logoURL UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_blob(statement,  11, [event.sourceService.logoBlob bytes], [event.sourceService.logoBlob length], SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  12, [event.sourceService.iconURL UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_blob(statement,  13, [event.sourceService.iconBlob bytes], [event.sourceService.iconBlob length], SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  14, [event.sourceService.websiteURL UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  15, [event.sourceUser.givenName UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  16, [event.sourceUser.middleName UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  17, [event.sourceUser.familyName UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  18, [event.sourceUser.honorificPrefix UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  19, [event.sourceUser.honorificSuffix UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  20, [event.sourceUser.birthdate UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  21, [event.sourceUser.politicalViews UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  22, [event.sourceUser.religiousViews UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  23, [event.sourceUser.gender UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  24, [event.sourceUser.locality UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  25, [event.sourceUser.region UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  26, [event.sourceUser.country UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  27, [event.sourceUser.postCode UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  28, [event.sourceUser.maritalStatus UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  29, [event.sourceUser.sexualOrientation UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_blob(statement,  30, [avatar bytes], [avatar length], SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  31, [event.sourceUser.avatarURL UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement,  32, [event.userIdentifier UTF8String], -1, SQLITE_TRANSIENT);
		
		int success = sqlite3_step(statement);
		if (success == SQLITE_ERROR) {
			// We will silently ignore insert errors, because we know we'll have duplicate primary key errors on refreshing most recent events.
			NSLog(@"%s", sqlite3_errmsg(database));
			// NSAssert1(0, @"Error: failed to insert Event into the database with message '%s'.", sqlite3_errmsg(database));
		}
		
		[self populateResources:database index:index];
		
		[avatar release];
	}
	else {
		NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
	}		
	sqlite3_finalize(statement);
}

// Populate Tags
-(void)populateTags:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO Tags (UserIdentifier, ContactIdentifier, Tag) VALUES (?, ?, ?)";
	Contact *contact = [myApp.contacts objectAtIndex:index];
	
	// Remove any old tags
	// TODO
	[self removeTags:database uid:myApp.userString cid:contact.contactIdentifier];

	for (int x=0; x<[contact.tags count]; x++) {
		Tag* tag = (Tag *)[contact.tags objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [tag.tag UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			
			if (success == SQLITE_ERROR) {
				// NSAssert1(0, @"Error: failed to insert Tag into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate URLs
-(void)populateURLs:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO URLs (UserIdentifier, ContactIdentifier, Type, URL) VALUES (?, ?, ?, ?)";
	Contact *contact = [myApp.contacts objectAtIndex:index];
	
	for (int x=0; x<[contact.urls count]; x++) {
		URL* url = (URL *)[contact.urls objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [url.type UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 4, [url.url UTF8String], -1, SQLITE_TRANSIENT);
						
			// Remove any old record
			// TODO
			[self removeURL:myApp.userString cid:contact.contactIdentifier tag:url.type];
			
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				// NSAssert1(0, @"Error: failed to insert URL into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate Phones
-(void)populatePhones:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO Phones (UserIdentifier, ContactIdentifier, Type, PrimaryPhone, PhoneNumber) VALUES (?, ?, ?, ?, ?)";
	Contact *contact = [myApp.contacts objectAtIndex:index];
	
	for (int x=0; x<[contact.phones count]; x++) {
		Phone* phone = (Phone *)[contact.phones objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [phone.type UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_int(statement,  4, phone.primary);
			sqlite3_bind_text(statement, 5, [phone.phoneNumber UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				// NSAssert1(0, @"Error: failed to insert Phone into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate IMs
-(void)populateIMs:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO IMs (UserIdentifier, ContactIdentifier, Network, PrimaryNetwork, UserName) VALUES (?, ?, ?, ?, ?)";
	Contact *contact = [myApp.contacts objectAtIndex:index];
	
	for (int x=0; x<[contact.imScreenNames count]; x++) {
		IM* im = (IM *)[contact.imScreenNames objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [im.network UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_int(statement,  4, im.primary);
			sqlite3_bind_text(statement, 5, [im.name UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				// NSAssert1(0, @"Error: failed to insert IM into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate Email Addresses
-(void)populateEmailAddresses:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO EmailAddresses (UserIdentifier, ContactIdentifier, Type, PrimaryAddress, Address) VALUES (?, ?, ?, ?, ?)";
	Contact *contact = [myApp.contacts objectAtIndex:index];
	
	for (int x=0; x<[contact.emailAddresses count]; x++) {
		EmailAddress* email = (EmailAddress *)[contact.emailAddresses objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [email.type UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_int(statement,  4, email.primary);
			sqlite3_bind_text(statement, 5, [email.address UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				// NSAssert1(0, @"Error: failed to insert Email Address into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate Addresses
-(void)populateAddresses:(sqlite3*) database index:(int)index {
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	sqlite3_stmt *statement;
	static char *sql = "INSERT INTO Addresses (UserIdentifier, ContactIdentifier, Type, PrimaryAddress, StreetAddress, Locality, Region, Country, PostCode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	Contact *contact = [myApp.contacts objectAtIndex:index];
	
	for (int x=0; x<[contact.addresses count]; x++) {
		Address* address = (Address *)[contact.addresses objectAtIndex:x];
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement, 1, [myApp.userString UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 2, [contact.contactIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 3, [address.type UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_int(statement,  4, address.primary);
			sqlite3_bind_text(statement, 5, [address.streetAddress UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 6, [address.locality UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 7, [address.region UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 8, [address.country UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 9, [address.postalCode UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				// NSAssert1(0, @"Error: failed to insert Address into the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		else {
			NSAssert1(0, @"Error: %s", sqlite3_errmsg(database));
		}		
		sqlite3_finalize(statement);
	}
}

// Populate Contact Tables
-(void)populateContactTables:(sqlite3 *)database index:(int)index {
	// Populate contacts
	[self populateContacts:database index:index];
	// Populate tags
	[self populateTags:database index:index];
	// Populate urls
	[self populateURLs:database index:index];
	// Populate phones
	[self populatePhones:database index:index];
	// Populate IMs
	[self populateIMs:database index:index];
	// Populate Email Address
	[self populateEmailAddresses:database index:index];
	// Populate Address
	[self populateAddresses:database index:index];
}

// Populate Events Tables
-(void)populateEventsTables:(sqlite3 *)database index:(int)index {
	// Populate events
	[self populateEvents:database index:index];
}

// Get database path
-(NSString*)getDatabasePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"cliqset.sqlite"];
	
	return path;
}

// Save contacts to database
-(void)saveContactsToDatabase {
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSAutoreleasePool *pool    = [[NSAutoreleasePool alloc] init];			
	
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		// Testing only!
		// [self clearTables:database];
		
		// Populate Contact Tables (Contacts, Addresses, EmailAddresses, etc) from Contacts Array
		// This loop is currently starving the main UI thread for users with lots of contacts
		for (int x=0; x<[myApp.contacts count]; x++) {
			[self populateContactTables:database index:x];
			}
		
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
	[pool release];
}

// Save events to database
-(void)saveEventsToDatabase {
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSAutoreleasePool *pool    = [[NSAutoreleasePool alloc] init];			

	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		// Testing only.
		// [self clearEventTables:database];
		
		// Populate Events Tables (Events, Resourcs, etc) from Events Array
		for (int x=0; x<[myApp.toEvents count]; x++) {
			[self populateEventsTables:database index:x];
		}
		
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
	[pool release];
}

// Create messages table
-(void)createMessagesTable {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		const char * sql = "select name from SQLite_Master where name='Messages'";
		sqlite3_stmt *statement;
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			if (sqlite3_step(statement) != SQLITE_ROW) {
				// Create Messages Table
				[self executeSqlite:database sql:"CREATE TABLE \"Messages\" (\"messageID\" VARCHAR PRIMARY KEY  NOT NULL , \"userID\" VARCHAR, \"givenName\" VARCHAR, \"familyName\" VARCHAR, \"Gender\" VARCHAR, \"avatar\" VARCHAR, \"avatarBlob\" BLOB, \"hometown\" VARCHAR, \"subject\" VARCHAR, \"recipients\" VARCHAR, \"timeSent\" VARCHAR, \"readFlag\" VARCHAR)"];		
				[self executeSqlite:database sql:"CREATE INDEX \"main\".\"timeSentKey\" ON \"Messages\" (\"timeSent\" DESC)"];
				}
			}
		
		sqlite3_finalize(statement);
		}
	else {
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_close(database);
}

// Save messages
-(void)saveMessages {

	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
		for (int x=0; x<[myApp.messages count]; x++) {
			const char * sql = "INSERT INTO Messages (messageID, userID, givenName, familyName, gender, avatar, avatarBlob, hometown, subject, recipients, timeSent, readFlag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			sqlite3_stmt *statement;
			Message * message = (Message *)[myApp.messages objectAtIndex:x];
			if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
				
				NSMutableData   * avatar     = [message.sender.avatarBlob copy];
				NSMutableString * recipients = [[NSMutableString alloc] init];
				
				BOOL bFirst = YES;
				for (int y=0; y<[message.recipients count]; y++) {
					if (bFirst) {
						recipients = [message.recipients objectAtIndex:y];
						bFirst = NO;
						} 
					else {
						recipients = [NSString stringWithFormat:@"%@, %@", recipients, [message.recipients objectAtIndex:y]];
						}
					}
				
				sqlite3_bind_text(statement,  1, [message.messageIdentifier UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  2, [message.sender.userIdentifier UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  3, [message.sender.givenName UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  4, [message.sender.familyName UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  5, [message.sender.gender UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  6, [message.sender.avatarURL UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_blob(statement,  7, [avatar bytes], [avatar length], SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  8, [message.sender.hometown UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement,  9, [message.subject UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement, 10, [recipients UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement, 11, [message.timeSent UTF8String], -1, SQLITE_TRANSIENT);
				sqlite3_bind_text(statement, 12, [message.read UTF8String], -1, SQLITE_TRANSIENT);

				int success = sqlite3_step(statement);
				if (success != SQLITE_OK) {
					// We will silently ignore insert errors, because we know we'll have duplicate primary key errors on refreshing most message events.
					// NSLog(@"%s", sqlite3_errmsg(database));
					// NSAssert1(0, @"Error: failed to insert Message into the database with message '%s'.", sqlite3_errmsg(database));
					}
				
				[avatar release];
				}
			
			sqlite3_finalize(statement);
			}		
	    }
	else {
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		}
	sqlite3_close(database);
}

// Load messages from database
-(void)loadMessageFromDatabase {
	
	cliqsetAppDelegate* myApp = (cliqsetAppDelegate*)[[UIApplication sharedApplication] delegate];
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		
			const char * sql = "SELECT messageID, userID, givenName, familyName, gender, avatar, avatarBlob, hometown, subject, recipients, timeSent, readFlag FROM Messages ORDER BY timeSent DESC";
			sqlite3_stmt *statement;
			if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
				while (sqlite3_step(statement) == SQLITE_ROW) {
					// Allocate new message record
					Message *message = [[Message alloc] init];
					
					message.messageIdentifier     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
					message.sender.userIdentifier = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
					message.sender.givenName      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
					message.sender.familyName     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
					message.sender.gender         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
					message.sender.avatarURL      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
					message.sender.avatarBlob     = [NSData dataWithBytes:sqlite3_column_blob(statement, 6) length:sqlite3_column_bytes(statement, 6)];
					message.sender.hometown       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
					message.subject               = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
					
					NSString * recipients         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
					NSArray * recipArray          = [recipients componentsSeparatedByString:@","];
					
					message.recipients            = (NSMutableArray*)recipArray;
					
					message.timeSent              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
					message.read                  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
					
					[myApp.messages addObject:message];
					[message release];
					}
				}
			
			sqlite3_finalize(statement);
		}		
	else {
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_close(database);
}

// Delete message from local database
-(void)deleteMessage:(NSString*)messageIdentifier {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "DELETE FROM Messages WHERE messageID=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [messageIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to remove Message from the database with message '%s'.", sqlite3_errmsg(database));
				}
			}
		//else {
		//	NSAssert1(0, @"Failed to prep statement with message '%s'.", sqlite3_errmsg(database));
		//	}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

// Mark message as being READ in the local database
-(void)markMessageRead:(NSString*)messageIdentifier {
	NSString *path = [self getDatabasePath];
	sqlite3 *database;
	
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		sqlite3_stmt *statement;
		static char *sql = "UPDATE Messages SET readFlag='true' WHERE messageID=?";
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			sqlite3_bind_text(statement,  1, [messageIdentifier UTF8String], -1, SQLITE_TRANSIENT);
			int success = sqlite3_step(statement);
			if (success == SQLITE_ERROR) {
				NSAssert1(0, @"Error: failed to update Message in the database with message '%s'.", sqlite3_errmsg(database));
			}
		}
		//else {
		//	NSAssert1(0, @"Failed to prep statement with message '%s'.", sqlite3_errmsg(database));
		//	}
    } else {
        // Even though the open failed, call close to properly clean up resources.
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
	sqlite3_close(database);
}

@end
